/*
see http://www.sqlite.org/  for more information on sqlite
see http://developer.mozilla.org for more information on the storage layer

see the TTRW2.xul file for additional comments

Logging
NSPR_LOG_MODULES mozStorage:5 

Change log
 Apr 21 2007  

	use  storageService   lastErrorString  for more information

 Apr 26 2007  

	changed to gjSQLonerror(e, strDB.value ," Error on Open "); for  openDatabase
 
*/

// a class to hold a few variables
function gjSQLHelper()
{
	this.colNameArray = new Array();
	this.resultsArray = new Array();
	this.statsArray   = new Array(2); // 0 = Rows, 1 = columnCount
}

// this function is called from TTRW2, the xul page that shows input boxes for an SQL query and 
// an SQL databse

function runStorage()
{
	debug("runStorage()");
	
	theResults = new gjSQLHelper;

	 //get the query string from an xul page
	 var strQuery = "SELECT date, duration FROM Day";

	 // get the file name and path to the database
	const CC = Components.classes; const CI = Components.interfaces;
	const TKCTRL_SERVICE_CID = "@takecontrol/service;1";

	var service = CC[TKCTRL_SERVICE_CID].getService().
		QueryInterface(CI.ITakeControlService);
		
	debug(service.storeFilePath + " " + service.storeFilePath.path);
/*
	var strDB = service.storeFilePath;
	
	debug(strDB);

	// nsILocalFile extends nsIFile, the input into Storage Service Open Data Base call
	var ifile = Components.classes["@mozilla.org/file/local;1"].
		createInstance(Components.interfaces.nsILocalFile);

	//if the database does not exist, storageService.openDatabase creates it
	ifile.initWithPath(strDB.value);

	//Warning,  if the file does not exist, openDatabase below will create if for you 
	if(!(ifile.exists()))
	{
		// addIt shows messages on the XUL page.  function addIt(where, what) 
		// It adds the what to whatever is already there on id where
		addIt('tb2', "File " + strDB.value + " does not exist, You are creating a new Database");
	}
	else
	{
		addIt('tb2', "File " + strDB.value + " exists");
	}
*/
	// get an interface to MOZSTORAGE
	var storageService = Components.classes["@mozilla.org/storage/service;1"]
	.getService(Components.interfaces.mozIStorageService);
 
	try
	{
		var mDBConn = storageService.openDatabase(service.storeFilePath);

		// if the db does not exist it does not give us any indication
		//	mDBConn.lastErrorString returns "not an error"

	}
	catch (e)
	{
			gjSQLonerror(e, service.storeFilePath.path ," Error on Open ");
	}

	// After and Open or Create of a DB above doSQL gets the column names and  
	// the data from the SQLite DB and stores it the helper object (theResults = new gjSQLHelper;) 

	if(doSQL(mDBConn,theResults, strQuery))
	{
			// display whatever results we have
			doDisplay (theResults);
	}

}

function doSQL(theConn, theHelper, thesql)
{
	debug("doSQL(" + theConn + ", " + theHelper + ", \"" + thesql + "\")");
	// if there is a problem with in the Select, Replace, Insert, Count, Delete  etc SQL statement 
	// if the table does not exist 
	// createStatement throws JS error.name == NS_ERROR_FAILURE, there is no return,the statement is undefined
	// theConn.lastErrorString contains the SQL Syntax error hint 

	// use the Step (Fetch a row at a time) method of using SQLite, so we keep track rows
	try
	{
		var myRows = 0;  
		var statement = theConn.createStatement(thesql);
		debug(statement);
	}
	catch (e)
	{
		debug("catch(" + e);
			// statement will be undefined because it throws error);
			gjSQLonerror(e, "Likely SQL syntax error: " + thesql, theConn.lastErrorString );
			//statement.reset;  //if it gets here and a statement does not exist, it says "Statement has no properties"
			return false;
	}

		// executeStep once to look at stats and get colnames etc (the first fetch)
		// store column names in colNameArray
		
	try
	{
		rc =  statement.executeStep();
		if(rc)
		{
      // if you use statement.columnCount in the for loops, it looks like it 
      // goes over to C++ land to get this already known value 
      var cols = statement.columnCount; 
			myRows++;
			for (var i = 0; i < cols; i++)
			{
				theHelper.colNameArray.push(statement.getColumnName(i));
			}
		}
		else
		{
				// might as well bail 
				addIt('tb3', "No Results from query " + thesql);
				statement.reset;
				return;
		}

			// store the results row wise, column wise in resultsArray vector AKA array of one dimension
			// row1 col1, row1, col2, row1 colx.. , row2 col1, row2 col2

			for (var i = 0; i < cols; i++)
			{
				theHelper.resultsArray.push(statement.getString(i));
			}

			// executeStep to get the rest of the results a row at a time

			while (statement.executeStep())
			{
				myRows++;
				for (var i = 0; i < cols; i++)
				{
					theHelper.resultsArray.push(statement.getString(i));
				}
			}

			addIt('tb2', " statement.numEntries " + statement.numEntries );  //? 
			addIt('tb2', " statement.columnCount " + cols );
			addIt('tb2', " myRows " + myRows );  // step walks through stuff so I do not think we know the count until the end

			addIt('tb2', " lastErrorString " + theConn.lastErrorString);

			//store the counts and stats in stats arrray

			theHelper.statsArray[1] = cols;
			theHelper.statsArray[0] = myRows;
	}
	catch (e)
	{
			gjSQLonerror(e, "Database Error ", theConn.lastErrorString );
	}

// what about cleaning up these arrays?
// the database is closed on html or xul page close 
// I think this is safe to reset here, if we do not get here, we are probably too hosed to do anything anyways

statement.reset;
return true;
}

// create a grid so we get nice spacing between our results

function doDisplay(theResults)
{
	debug("doDisplay()");
	// chain is VBOX gjtheGrid, groupbox, grid, columns, rows, row, label

		var vb= document.getElementById('tkctrl-the-grid');
		// could not get a grid to work unless inside a groupbox
		var gb = document.createElement("groupbox"); 
		gb.setAttribute("style","width:100%" ); // spacing overrides do not work as yet
		var tg = document.createElement("grid");
		var cols = document.createElement("columns");
		var rs = document.createElement("rows");

		for(var i = 0; i < 	theResults.statsArray[1]; i++)
		{
			var col = document.createElement("column");
			col.setAttribute("flex", 1);
			cols.appendChild(col);
		}

		tg.appendChild(cols);

		var r = document.createElement("row");
		
		var percent =parseInt(100 / theResults.statsArray[1]);
		var pctStr= percent + "%";
		var myStyle = "font-weight:bolder; font-size:larger; width:" + pctStr;

		for (var i = 0; i < theResults.statsArray[1]; i++)
		{
			var l = document.createElement("label");
			l.setAttribute("value", theResults.colNameArray[i].toString());
			l.setAttribute("style",myStyle  );
			r.appendChild(l);			
		}
		rs.appendChild(r);

		var colrow=0;
		for (var c = 0; c < theResults.statsArray[0]; c++)
		{
			r = document.createElement("row");
			for (var i = 0; i < theResults.statsArray[1]; i++)
			{

				var l = document.createElement("label");
				l.setAttribute("value", theResults.resultsArray[colrow].toString());
				r.appendChild(l);
				colrow++;	
			}
			rs.appendChild(r);
		}

	tg.appendChild(cols);
	tg.appendChild(rs);
	gb.appendChild(tg);
	vb.appendChild(gb);

return;
}

// display errors and warnings on the page instead of alert boxes

function addIt(where, what)
{
	debug("addIt(" + where + ", " + what + ")");
	// get the current value and add something to it
	var tmp;
	tmp = document.getElementById(where).value
	tmp += what;
	document.getElementById(where).value = tmp;
}

function gjSQLonerror(ex, msg, SQLmsg )
{
	debug("gjSQLonerror()");
	//document.getElementById('err').hidden = 'false';
	theErrbox = document.getElementById('err');
	theErrbox.removeAttribute("hidden");

	try
	{
		if( ex.name == "NS_ERROR_FAILURE")
		{
			addIt('tb3',  msg + "[ " + SQLmsg + " ]" );
			addIt('tb4', "Error Name: " + ex.name + "  Error Message: " + ex.message);
		}
		else
		{
			addIt('tb3',": " + msg + " Error Name: " + ex.name) ;
			addIt('tb4',"Error Message: " + ex.message + " file: " + ex.fileName  + " line: " + ex.lineNumber);
		}
	}
	catch(error)
	{
			alert("Component Error " + msg + "  1: " + ex.name + "  2: " + ex.message);
			addit('tb3', "Component Error " + msg + "  1: " + ex.name + "  2: " + ex.message);
	}
return;
}

var debug = function (s) { dump("tkctrl report: " + s + "\n"); }















